<?php

namespace app\admin\controller\dahui;

use app\common\controller\Backend;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use think\Db;
use think\Exception;
use think\exception\PDOException;
use think\exception\ValidateException;


/**
 * 新退款管理
 *
 * @icon fa fa-circle-o
 */
class Newtk extends Backend
{
    
    /**
     * Newtk模型对象
     * @var \app\admin\model\dahui\Newtk
     */
    protected $model = null;

    protected $noNeedRight = ['changetstatus', 'changetstatus4','chuhe', 'fuhe', 'plchuhe', 'plfuhe', 'pltk', 'jcsfz', 'chuhefuhe', 'import0'];

    protected $tuikuan = 0;
    protected $caozuo = 0;
    protected $audit = 0;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\dahui\Newtk;
        $this->view->assign("tkxztList", $this->model->getTkxztList());
        $this->view->assign("refundornotList", $this->model->getRefundornotList());
        $this->view->assign("idcardstateList", $this->model->getIdcardstateList());

        $this->tuikuan = $this->auth->check('dahui/newtk/tuikuan');
        $this->caozuo = $this->auth->check('dahui/dingdan/caozuo');
        $this->audit = $this->auth->check('dahui/dingdan/audit');

        $this->assignconfig('audit', $this->auth->check('dahui/dingdan/audit'));
        $this->assignconfig('luru', $this->auth->check('dahui/dingdan/luru'));
        $this->assignconfig('caozuo', $this->auth->check('dahui/dingdan/caozuo'));

        $this->assignconfig('chuhe', $this->auth->check('dahui/newtk/chuhe'));
        $this->assignconfig('fuhe', $this->auth->check('dahui/newtk/fuhe'));
        $this->assignconfig('tuikuan', $this->auth->check('dahui/newtk/tuikuan'));

    }

    /**
     * 添加
     */
    public function add()
    {
        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");
            if ($params) {
                $params = $this->preExcludeFields($params);

                if ($this->dataLimit && $this->dataLimitFieldAutoFill) {
                    $params[$this->dataLimitField] = $this->auth->id;
                }
                $result = false;
                Db::startTrans();
                try {
                    //是否采用模型验证
                    if ($this->modelValidate) {
                        $name = str_replace("\\model\\", "\\validate\\", get_class($this->model));
                        $validate = is_bool($this->modelValidate) ? ($this->modelSceneValidate ? $name . '.add' : $name) : $this->modelValidate;
                        $this->model->validateFailException(true)->validate($validate);
                    }
                    $params['djman'] = $this->auth->nickname;
                    $result = $this->model->allowField(true)->save($params);
                    Db::commit();
                } catch (ValidateException $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                } catch (PDOException $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                } catch (Exception $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                }
                if ($result !== false) {
                    $this->success();
                } else {
                    $this->error(__('No rows were inserted'));
                }
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }
        return $this->view->fetch();
    }


    public function import()
    {
        $file = $this->request->request('file');
        if (!$file) {
            $this->error(__('Parameter %s can not be empty', 'file'));
        }
        $filePath = ROOT_PATH . DS . 'public' . DS . $file;
        if (!is_file($filePath)) {
            $this->error(__('No results were found'));
        }
        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv'])) {
            $this->error(__('Unknown data format'));
        }

        $num = 0;
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $n = 0;
            $lasttaobaoname = "";
            $lastline = "";
            $max = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding != 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                $oldchar = array("\t", "\"");
                $newchar = array("", "");
                $line = str_replace($oldchar, $newchar, $line);

                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    $filed = explode(',', $line);
                    $max = count($filed);
                    if ($max < 16 || $filed[0] != '序号' || $filed[1] != '淘宝名' || $filed[2] != '缴纳人姓名' || $filed[3] != '支付宝'
                        || $filed[4] != '退款人姓名' || $filed[5] != '实退金额' || $filed[6] != '身份证' || $filed[7] != '缴纳日期'
                        || $filed[8] != '月数' || $filed[9] != '缴纳基数' || $filed[10] != '缴纳金额' || $filed[11] != '新金额'
                        || $filed[12] != '应退金额' || $filed[13] != '公司' || $filed[14] != '备注' || $filed[15] != '制表') {
                        $this->error(__('错误的格式！'));
                    }
                    //表头
                    //序号	淘宝名	缴纳人姓名	支付宝	退款人姓名	实退金额	身份证	缴纳日期	月数	缴纳基数	缴纳金额	新金额	应退金额	公司	备注

                } else {
                    //内容
                    $line = $lastline.$line;
                    $filed = explode(',', $line);
                    $size = count($filed);
                    if ($size != $max) {
                        $lastline = $line;
                        $n++;
                        continue;
                    }

                    if (!empty($filed[0])) {
                        $lastline = "";
                        $idnumber = strtoupper(trim($filed[6]));
//                        $one =\app\admin\model\dahui\Newtk::where('idnumber', $idnumber)->find();
//                        if ($one) {
//                            continue;//身份证重复则去除
//                        }

                        $model = new \app\admin\model\dahui\Newtk;
                        $model->taobaoname = trim($filed[1]);
                        if (empty($model->taobaoname)) {
                            $model->taobaoname = $lasttaobaoname;
                        }
                        $model->name = trim($filed[2]);
                        $model->zfbname = trim($filed[3]);
                        $model->tkname = trim($filed[4]);
                        $model->stje = trim($filed[5]);
                        $model->idnumber = $idnumber;//

                        if ($this->isValid($model->idnumber)) {
                            $model->idcardstate = "正确";
                        } else {
                            $model->idcardstate = "错误";
                        }

                        $model->jndate = trim($filed[7]);
                        $model->monthspaid = trim($filed[8]);
                        $model->paymentbase = trim($filed[9]);
                        $model->paymentamount = trim($filed[10]);
                        $model->newje = trim($filed[11]);
                        $model->ytje = trim($filed[12]);
                        $model->payingcompany = trim($filed[13]);
                        $model->remark = trim($filed[14]);

                        $model->djman = trim($filed[15]);//制表
                        $model->save();
                        $num++;
                        if ($num == 249) {
                            $num = $num;
                        }
                        $lasttaobaoname = $filed[1];
                    }
                }
                $n++;
            }
            fclose($file);

        }

        $this->success("成功导入记录 ".$num);
    }

    
    //http://dahui.test/admin/dahui/newtk/changetstatus
    public function changetstatus($ids = null)
    {
        $row = $this->model->get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        if ($this->tuikuan) {
            if ($row->refundornot == '未退款') {
                $row->refundornot = '已退款';
                $row->tkdate = date('Y-m-d H:i:s');
            } else if ($row->refundornot == '已退款') {
                $row->refundornot = '退款失败';
                $row->tkdate = date('Y-m-d H:i:s');
            } else if ($row->refundornot == '退款失败') {
                $row->refundornot = '已退款';
                $row->tkdate = date('Y-m-d H:i:s');
            }
        } else if ($this->caozuo) {
            if ($row->refundornot == '') {
                $row->refundornot = '未退款';
            } else if ($row->refundornot == '未退款') {
                $row->refundornot = '';
            }
        } else if ($this->audit) {
            if ($row->refundornot == '') {
                $row->refundornot = '未退款';
            } else if ($row->refundornot == '未退款') {
                $row->refundornot = '';
            }
        }


        $row->save();
        $this->success("退款状态修改成功", null, ['refundornot' => $row->refundornot]);

    }

    //退款员
    //从待退款状态 点击后 变更为 已退款（另外有时候误操作情况下 他可以把已退款变更为  --状态 让其他角色重新操作）
    public function changetstatus4($ids = null)
    {
        $row = $this->model->get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        if ($row->refundornot == '未退款') {
            $row->refundornot = '已退款';
            $row->tkdate = date('Y-m-d H:i:s');
        } else if ($row->refundornot == '已退款') {
            $row->refundornot = '退款失败';
            $row->tkdate = date('Y-m-d H:i:s');
        } else if ($row->refundornot == '退款失败') {
            $row->refundornot = '已退款';
            $row->tkdate = date('Y-m-d H:i:s');
        }
//        $row = $this->setquanxianlog2($row);

        $row->save();
        $this->success("退款状态修改成功", null, ['refundornot' => $row->refundornot]);

    }

    //http://dahui.test/admin/dahui/newtk/chuhe
    public function chuhe($ids = null)
    {
        $row = $this->model->get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        if ($row->tkxzt == '待初审') {
            $row->tkxzt = '待终审';
            $row->chman = $this->auth->nickname;
        }
//        else if ($row->tkxzt == '待终审') {
//            $row->tkxzt = '待初审';
//        }

        $row->save();
        $this->success("审核状态修改成功", null, ['tkxzt' => $row->tkxzt]);
    }

    //http://dahui.test/admin/dahui/newtk/fuhe
    public function fuhe($ids = null)
    {
        $row = $this->model->get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        if ($row->tkxzt == '待终审') {
            $row->tkxzt = '已终审';
            $row->fhman = $this->auth->nickname;
        }
//        else if ($row->tkxzt == '已终审') {
//            $row->tkxzt = '待终审';
//        }

        $row->save();
        $this->success("审核状态修改成功", null, ['tkxzt' => $row->tkxzt]);

    }

    //http://dahui.test/admin/dahui/newtk/chuhefuhe
    public function chuhefuhe($ids = null)
    {
        $row = $this->model->get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        if ($row->tkxzt == '待初审') {
            $row->chman = $this->auth->nickname;
            $row->tkxzt = '待终审';
        } else if ($row->tkxzt == '待终审') {
            $row->fhman = $this->auth->nickname;
            $row->tkxzt = '已终审';
        }
//        else if ($row->tkxzt == '已终审') {
//            $row->tkxzt = '待初审';
//        }

        $row->save();
        $this->success("审核状态修改成功", null, ['tkxzt' => $row->tkxzt]);
    }

    /**
     * 批量初核
     */
    public function plchuhe($ids = null)
    {
        $list = $this->model
            ->where('id', 'in', $ids)
            ->select();

        $chuhe = $this->auth->check('dahui/newtk/chuhe');
        if (!$chuhe) {
            $this->error(__('无权限'));
        }

        foreach ($list as $row) {
            $row->tkxzt = '待终审';
            $row->chman = $this->auth->nickname;
            $row->save();
        }

        $this->success("批量初核成功");

    }

    /**
     * 批量复核
     */
    public function plfuhe($ids = null)
    {
        $list = $this->model
            ->where('id', 'in', $ids)
            ->select();

        $fuhe = $this->auth->check('dahui/newtk/fuhe');
        if (!$fuhe) {
            $this->error(__('无权限'));
        }
        foreach ($list as $row) {
            $row->tkxzt = '已终审';
            $row->fhman = $this->auth->nickname;
            $row->save();
        }

        $this->success("批量复核成功");

    }

    /**
     * 批量退款
     */
    public function pltk($ids = null)
    {
        $list = $this->model
            ->where('id', 'in', $ids)
            ->select();

        foreach ($list as $row) {
            $row->refundornot = '已退款';
            $row->tkdate = date('Y-m-d H:i:s');
            $row->save();
        }

        $this->success();

    }

    //update fa_dahui_newtk set idnumber = upper(idnumber)
    public function jcsfz()
    {
        $list = \app\admin\model\dahui\Newtk::all();

        foreach ($list as $row) {
            $total = \app\admin\model\dahui\Newtk::where('idnumber', $row->idnumber)
                ->count();
            if ($total > 1) {
                $row->idcardstate = "重复";
            } else {
                if ($this->isValid($row->idnumber)) {
                    $row->idcardstate = "正确";
                } else {
                    $row->idcardstate = "错误";
                }
            }
            $row->save();
        }

        $this->success();

    }

    /**
     * 校验身份证号是否合法
     * @param string $num 待校验的身份证号
     * @return bool
     */
    function isValid($num)
    {
        //老身份证长度15位，新身份证长度18位
        $length = strlen($num);
        if ($length == 15) { //如果是15位身份证

            //15位身份证没有字母
            if (!is_numeric($num)) {
                return false;
            }
            // 省市县（6位）
            $areaNum = substr($num, 0, 6);
            // 出生年月（6位）
            $dateNum = substr($num, 6, 6);

        } else if ($length == 18) { //如果是18位身份证

            //基本格式校验
            if (!preg_match('/^\d{17}[0-9xX]$/', $num)) {
                return false;
            }
            // 省市县（6位）
            $areaNum = substr($num, 0, 6);
            // 出生年月日（8位）
            $dateNum = substr($num, 6, 8);

        } else { //假身份证
            return false;
        }

        //验证地区
        if (!self::isAreaCodeValid($areaNum)) {
            return false;
        }

        //验证日期
        if (!self::isDateValid($dateNum)) {
            return false;
        }

        //验证最后一位
        if (!self::isVerifyCodeValid($num)) {
            return false;
        }

        return true;
    }

    /**
     * 省市自治区校验
     * @param string $area 省、直辖市代码
     * @return bool
     */
    function isAreaCodeValid($area)
    {
        $provinceCode = substr($area, 0, 2);

        // 根据GB/T2260—999，省市代码11到65
        if (11 <= $provinceCode && $provinceCode <= 65) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 验证出生日期合法性
     * @param string $date 日期
     * @return bool
     */
    function isDateValid($date)
    {
        if (strlen($date) == 6) { //15位身份证号没有年份，这里拼上年份
            $date = '19' . $date;
        }
        $year = intval(substr($date, 0, 4));
        $month = intval(substr($date, 4, 2));
        $day = intval(substr($date, 6, 2));

        //日期基本格式校验
        if (!checkdate($month, $day, $year)) {
            return false;
        }

        //日期格式正确，但是逻辑存在问题(如:年份大于当前年)
        $currYear = date('Y');
        if ($year > $currYear) {
            return false;
        }
        return true;
    }

    /**
     * 验证18位身份证最后一位
     * @param string $num 待校验的身份证号
     * @return bool
     */
    function isVerifyCodeValid($num)
    {
        if (strlen($num) == 18) {
            $factor = [7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2];
            $tokens = ['1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2'];

            $checkSum = 0;
            for ($i = 0; $i < 17; $i++) {
                $checkSum += intval($num{$i}) * $factor[$i];
            }

            $mod = $checkSum % 11;
            $token = $tokens[$mod];

            $lastChar = strtoupper($num{17});

            if ($lastChar != $token) {
                return false;
            }
        }
        return true;
    }

    //
    public function import0()
    {
        list($where, $sort, $order, $offset, $limit) = $this->buildparams();
        $count = $this->model
            ->where($where)
            ->count();

        $fileName = "疫情社保退款";

        set_time_limit(0);
        ini_set('memory_limit', '-1');

        header('Content-Type: application/vnd.ms-execl');
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');

        //打开php标准输出流
        //以写入追加的方式打开
        $fp = fopen('php://output', 'a');

        //一次取5000条数据
        $step = ceil($count/5000);

        $title = array('淘宝名', '缴纳人姓名', '身份证号码', '退款月份', '退款月数',
            '缴纳基数', '应缴纳金额', '实际缴纳金额', '支付宝账号', '支付宝姓名', '退款金额', '制表',
            '初核', '复核', '退款信息状态', '退款状态', '备注', '退款日期');

        //将标题写到标准输出中
        fputcsv($fp, $title);

        for($s = 0; $s < $step; $s++) {
            $page_count = $s;
            $page_size = 5000;
            $offset = $page_count * $page_size;
            $list = $this->model
                ->where($where)
                ->limit($offset, $page_size)
                ->select();
            $data = collection($list)->toArray();

            foreach ($data as &$row) {
                $v = array(
                    $row['taobaoname'],
                    $row['name'],
                    "\t" .$row['idnumber'],
                    $row['jndate'],
                    $row['monthspaid'],
                    $row['paymentbase'],
                    $row['paymentamount'],
                    $row['newje'],
                    $row['zfbname'],
                    $row['tkname'],
                    $row['ytje'],
                    $row['djman'],
                    $row['chman'],
                    $row['fhman'],
                    $row['tkxzt'],
                    $row['refundornot'],
                    $row['remark'],
                    $row['tkdate']
                );

                fputcsv($fp, $v);
            }
            //每5000条数据就刷新缓冲区
            ob_flush();
            flush();
        }
        exit();
    }

}
